CREATE procedure [dbo].[sp_asi_FRDonations]
@Id varchar(10),
@IncludeOutrightGifts bit=0,
@IncludePledges bit=0,
@IncludeSoftCredit bit=0,
@IncludeMatching bit=0,
@IncludeInKind bit=0,
@IncludeWrittenOff bit=0,
@IncludeFundRaising bit=0,
@IncludeEvents bit=0,
@IncludeDues bit=0,
@FundFilter varchar(255)='',
@CampaignFilter varchar(255)='',
@DistributionFilter varchar(255)='',
@FromDate datetime='',
@ThroughDate datetime='',
@FromAmount money=0.00,
@ThroughAmount money=0.00,
@SelectType varchar(40)='',
@DoSplits bit=0,
@TopLast int=0,
@TopWhat varchar(10)='',
@TopYears int=0,
@IncludeMidTerm bit=0,
@OnlyPaidPortion bit=0
as
set nocount on
declare
@TransNum int,
@Fund varchar(20),
@PrevTransNum int,
@PrevFund varchar(20),
@Source varchar(20),
@Product varchar(31),
@PrevProduct varchar(31),
@ProductMajor varchar(15),
@EventCode varchar(10),
@InvoiceRefNum int,
@Sql varchar(1000),
@Year varchar(4),
@PrevYear varchar(4),
@Amount money,
@PrevAmount money,
@RoundAmount money,
@YTD money,
@Lifetime money,
@Largest money,
@DateLargest datetime,
@DonorSince datetime,
@CurrentYear varchar(10),
@Campaign varchar(10),
@PrevCampaign varchar(10),
@Include int,
@MidTermAmount money,
@PledgeLifetime money,
@PledgeLargest money,
@DatePledgeLargest datetime,
@DateGiftLast datetime,
@DatePledgeLast datetime,
@PledgePaid money,
@PledgeBalance money,
@GiftType varchar(20),
@HistoryRowCount int
create table #FRDonationTemp (
ID varchar(10),
OriginalTransNumber int,
TransactionNumber int,
InvoiceRefNum int,
SourceSystem varchar(20),
TransactionDate datetime,
Amount money,
AdjustmentAmount money,
PaymentAmount money,
Product varchar(31),
Appeal varchar(40),
Campaign varchar(10),
Fund varchar(10),
GiftType varchar(20),
MatchOrSoftCredit varchar(20),
PaymentType varchar(15),
AdjustmentFlag varchar(3),
ProductMajor varchar(15),
MemTribId varchar(10),
MemTribNameText varchar(100),
ListAs varchar(255),
SolicitorId varchar(10),
FiscalPeriod int,
Include int
)
create table #FRDonation (
ID varchar(10),
OriginalTransNumber int,
TransactionNumber int,
InvoiceRefNum int,
SourceSystem varchar(20),
TransactionDate datetime,
Amount money,
AdjustmentAmount money,
PaymentAmount money,
Product varchar(31),
Appeal varchar(40),
Campaign varchar(10),
Fund varchar(10),
GiftType varchar(20),
MatchOrSoftCredit varchar(20),
PaymentType varchar(15),
AdjustmentFlag varchar(3),
ProductMajor varchar(15),
MemTribId varchar(10),
MemTribNameText varchar(100),
ListAs varchar(255),
SolicitorId varchar(10),
FiscalPeriod int,
Include int
)
create table #Trends (
TransactionNumber int,
TheYear varchar(4),
Amount money,
Calc int
)
create table #FRPayment (
ID varchar(10),
OriginalTransNumber int,
TransactionNumber int,
DateReceived datetime,
InvoiceRefNum int,
PaymentAmount money,
GiftType varchar(20),
FiscalPeriod int
)
create table #FRDonationHistory (
TransactionNumber int,
InvoiceRefNum int,
SourceSystem varchar(20),
TransactionDate datetime,
Amount money,
PaymentAmount money,
Product varchar(31),
Campaign varchar(10),
Fund varchar(10),
GiftType varchar(20),
MatchOrSoftCredit varchar(20),
AdjustmentFlag varchar(3),
Include int
)
truncate table #FRDonationTemp
truncate table #FRDonation
truncate table #Trends
truncate table #FRPayment
truncate table #FRDonationHistory
if @IncludeEvents=1
BEGIN
insert into #FRDonation(ID,OriginalTransNumber,TransactionNumber,InvoiceRefNum,SourceSystem,
TransactionDate,Amount,AdjustmentAmount,PaymentAmount,Product,Appeal,Campaign,Fund,GiftType,
MatchOrSoftCredit,PaymentType,AdjustmentFlag,ProductMajor,MemTribId,MemTribNameText,ListAs,
SolicitorId,FiscalPeriod,Include)
select max(Trans.ST_ID)ID,
min(Trans.TRANS_NUMBER)as OriginalTransNum,
min(Trans.TRANS_NUMBER)TransactionNumber,
max(Trans.INVOICE_REFERENCE_NUM) InvoiceRefNum,
'Event' as SourceSystem,
max(Trans.TRANSACTION_DATE)TransactionDate,
(sum(Trans.AMOUNT) * -1) as Amount,
0 as AdjustmentAmount,
0 as PaymentAmount,
max(Trans.PRODUCT_CODE) Product,
max(Trans.SOURCE_CODE)Appeal,
max(Trans.CAMPAIGN_CODE)Campaign,
Max(Trans.GL_ACCT_ORG_CODE) Fund,
'Gift' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'' as AdjustmentFlag,
max(p.PRODUCT_MAJOR) ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
max(Trans.FISCAL_PERIOD) FiscalPeriod,
0 as Include
from Trans
inner join Product p on p.PRODUCT_CODE=Trans.PRODUCT_CODE
where Trans.SOURCE_SYSTEM = 'MEETING'
and Trans.JOURNAL_TYPE = 'IN' and Trans.TRANSACTION_TYPE = 'DIST'
and Trans.IS_FR_ITEM = 1 and Trans.POSTED >= 2 and Trans.ST_ID=@Id
group by Trans.ST_ID, Trans.INVOICE_REFERENCE_NUM, Trans.GL_ACCT_ORG_CODE, Trans.PRODUCT_CODE
END
delete #FRDonation where Amount=0.00
if @IncludeDues=1
BEGIN
insert into #FRDonation(ID,OriginalTransNumber,TransactionNumber,InvoiceRefNum,SourceSystem,
TransactionDate,Amount,AdjustmentAmount,PaymentAmount,Product,Appeal,Campaign,Fund,GiftType,
MatchOrSoftCredit,PaymentType,AdjustmentFlag,ProductMajor,MemTribId,MemTribNameText,ListAs,
SolicitorId,FiscalPeriod,Include)
select max(Trans.ST_ID)ID,
min(Trans.TRANS_NUMBER)as OriginalTransNum,
min(Trans.TRANS_NUMBER)TransactionNumber,
max(Trans.INVOICE_REFERENCE_NUM) InvoiceRefNum,
'Dues' as SourceSystem,
max(Trans.TRANSACTION_DATE)TransactionDate,
(sum(Trans.AMOUNT) * -1) as Amount,
0 as AdjustmentAmount,
0 as PaymentAmount,
max(Trans.PRODUCT_CODE) Product,
max(Trans.SOURCE_CODE)Appeal,
max(Trans.CAMPAIGN_CODE)Campaign,
Max(Trans.GL_ACCT_ORG_CODE) Fund,
'Pledge' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'' as AdjustmentFlag,
'' as ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
max(Trans.FISCAL_PERIOD) FiscalPeriod,
0 as Include
from Trans
where Trans.SOURCE_SYSTEM = 'DUES'
and Trans.JOURNAL_TYPE = 'IN' and Trans.TRANSACTION_TYPE = 'DIST'
and Trans.IS_FR_ITEM = 1 and Trans.POSTED >= 2 and Trans.ST_ID=@Id
group by Trans.ST_ID, Trans.INVOICE_REFERENCE_NUM, Trans.GL_ACCT_ORG_CODE, Trans.PRODUCT_CODE
UNION ALL
select max(Trans.ST_ID)ID,
min(Trans.TRANS_NUMBER)as OriginalTransNum,
min(Trans.TRANS_NUMBER)TransactionNumber,
max(Trans.INVOICE_REFERENCE_NUM) InvoiceRefNum,
'Dues' as SourceSystem,
max(Trans.TRANSACTION_DATE)TransactionDate,
(sum(Trans.AMOUNT) * -1) as Amount,
0 as AdjustmentAmount,
0 as PaymentAmount,
max(Trans.PRODUCT_CODE) Product,
max(Trans.SOURCE_CODE)Appeal,
max(Trans.CAMPAIGN_CODE)Campaign,
Max(Trans.GL_ACCT_ORG_CODE) Fund,
'Gift' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'' as AdjustmentFlag,
'' as ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
max(Trans.FISCAL_PERIOD) FiscalPeriod,
0 as Include
from Trans
where (Trans.SOURCE_SYSTEM = 'DUES' or Trans.SOURCE_SYSTEM = 'SC')
and Trans.JOURNAL_TYPE = 'PAY' and Trans.TRANSACTION_TYPE = 'DIST'
and Trans.IS_FR_ITEM = 1 and Trans.POSTED >= 2 and Trans.ST_ID=@Id
group by Trans.ST_ID, Trans.TRANS_NUMBER, Trans.GL_ACCT_ORG_CODE, Trans.PRODUCT_CODE
END
if @IncludeSoftCredit=1
BEGIN
insert into #FRDonation(ID,OriginalTransNumber,TransactionNumber,InvoiceRefNum,SourceSystem,
TransactionDate,Amount,AdjustmentAmount,PaymentAmount,Product,Appeal,Campaign,Fund,GiftType,
MatchOrSoftCredit,PaymentType,AdjustmentFlag,ProductMajor,MemTribId,MemTribNameText,ListAs,
SolicitorId,FiscalPeriod,Include)
select Max(Trans_SoftCredit.SOFT_CREDIT_ID) ID,
max(Trans_SoftCredit.TRANS_NUMBER)OriginalTransNum,
max(Trans_SoftCredit.TRANS_NUMBER)TransactionNumber,
'' as InvoiceRefNum,
'Fundraising' as SourceSystem,
max(Activity.TRANSACTION_DATE) as TransactionDate,
sum(Trans_SoftCredit.AMOUNT + dbo.fn_asi_SoftCreditCMDM_Amount(Trans_SoftCredit.TRANS_NUMBER,Trans_SoftCredit.ORIGINATING_ACTIVITY_SEQN,Trans_SoftCredit.SOFT_CREDIT_ID)) as Amount,
0 as AdjustmentAmount,
0 as PaymentAmount,
max(Trans_SoftCredit.PRODUCT_CODE) as Product,
max(Activity.SOURCE_CODE) as Appeal,
max(Activity.CAMPAIGN_CODE) as Campaign,
max(Activity.ORG_CODE) as Fund,
(case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then 'Gift' else 'Pledge' end) as GiftType,
'Soft Credit' as MatchOrSoftCredit,
case when max(Cash_Accounts.ACCOUNT_TYPE) = 0 then 'Cash'
when max(Cash_Accounts.ACCOUNT_TYPE) = 1 then 'Credit Card'
when max(Cash_Accounts.ACCOUNT_TYPE) = 2 then 'In Kind'
else ''
end
as PaymentType,
max(case dbo.fn_asi_SoftCreditCMDM_Exists(Trans_SoftCredit.TRANS_NUMBER,Trans_SoftCredit.ORIGINATING_ACTIVITY_SEQN,Trans_SoftCredit.SOFT_CREDIT_ID)
when 1 then 'Yes'
else ''
end) as AdjustmentFlag,
'' as ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
max(Trans.FISCAL_PERIOD) FiscalPeriod,
0 as Include
from Trans_SoftCredit
inner join Activity on Activity.SEQN = Trans_SoftCredit.ORIGINATING_ACTIVITY_SEQN
inner join Trans on Trans.TRANS_NUMBER = Trans_SoftCredit.TRANS_NUMBER
left outer join Cash_Accounts on Trans.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
where Activity.SOURCE_SYSTEM='FR'
and Trans.LINE_NUMBER = 1 AND Trans.SUB_LINE_NUMBER = 1
and Trans.POSTED >= 2 and Trans_SoftCredit.SOFT_CREDIT_ID=@Id
and Trans_SoftCredit.TRANS_NUMBER=Activity.ORIGINATING_TRANS_NUM
group by Trans_SoftCredit.SOFT_CREDIT_ID, Trans_SoftCredit.TRANS_NUMBER, Activity.ORG_CODE, Trans_SoftCredit.PRODUCT_CODE
END
if @IncludeFundRaising=1
BEGIN
insert into #FRDonationTemp(ID,OriginalTransNumber,TransactionNumber,InvoiceRefNum,SourceSystem,
TransactionDate,Amount,AdjustmentAmount,PaymentAmount,Product,Appeal,Campaign,Fund,GiftType,
MatchOrSoftCredit,PaymentType,AdjustmentFlag,ProductMajor,MemTribId,MemTribNameText,ListAs,
SolicitorId,FiscalPeriod,Include)
select max(P.BT_ID) ID,
(case when max(P.IS_MATCH_GIFT)=1 then max(P.MATCH_GIFT_TRANS_NUM) else max(P.TRANS_NUMBER) end) as OriginalTransNumber,
max(P.TRANS_NUMBER) TransactionNumber,
max(P.INVOICE_REFERENCE_NUM) InvoiceRefNum,
'Fundraising' as SourceSystem,
max(P.TRANSACTION_DATE) TransactionDate,
(sum(P.AMOUNT) * -1) as Amount,
0 as AdjustmentAmount,
0 as PaymentAmount,
max(P.PRODUCT_CODE) Product,
max(P.SOURCE_CODE) Appeal,
max(P.CAMPAIGN_CODE) Campaign,
max(P.OWNER_ORG_CODE) Fund,
(case when max(P.INSTALL_BILL_DATE) is null then 'Gift' else 'Pledge' end) as GiftType,
(case when max(P.IS_MATCH_GIFT)= 1 then 'Matching' else '' end) as MatchOrSoftCredit,
case when max(Cash_Accounts.ACCOUNT_TYPE) = 0 then 'Cash'
when max(Cash_Accounts.ACCOUNT_TYPE) = 1 then 'Credit Card'
when max(Cash_Accounts.ACCOUNT_TYPE) = 2 then 'In Kind'
else ''
end
as PaymentType,
'' as AdjustmentFlag,
'' as ProductMajor,
max(P.MEM_TRIB_ID) as MemTribId,
max(P.MEM_TRIB_NAME_TEXT) MemTribNameText,
max(P.ACTION_CODES) as ListAs,
max(P.SOLICITOR_ID) as SolicitorId,
max(P.FISCAL_PERIOD) FiscalPeriod,
0 as Include
from Trans P
inner join Invoice I on I.REFERENCE_NUM=P.INVOICE_REFERENCE_NUM
left outer join Cash_Accounts on P.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
where
P.JOURNAL_TYPE = 'IN' and P.TRANSACTION_TYPE = 'DIST'
and P.SOURCE_SYSTEM='FR' and P.POSTED >= 2 and P.BT_ID=@Id
group by P.BT_ID, P.TRANS_NUMBER, P.OWNER_ORG_CODE, P.PRODUCT_CODE
UNION ALL
select max(C.BT_ID) ID,
'' as OriginalTransNumber,
max(Invoice.ORIGINATING_TRANS_NUM) TransactionNumber,
max(C.INVOICE_REFERENCE_NUM) InvoiceRefNum,
'Fundraising' as SourceSystem,
'' as TransactionDate,
(sum(C.AMOUNT) * -1) as Amount,
(sum(C.AMOUNT) * -1) as AdjustmentAmount,
0 as PaymentAmount,
max(C.PRODUCT_CODE) Product,
'' as Appeal,
'' as Campaign,
max(C.OWNER_ORG_CODE) Fund,
'' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'Yes' as AdjustmentFlag,
'' as ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
0 as FiscalPeriod,
0 as Include
from Trans C
inner join Invoice on Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
where Invoice.SOURCE_SYSTEM='FR'
and ((C.JOURNAL_TYPE = 'CM' and C.TRANSACTION_TYPE = 'DIST')
or (C.JOURNAL_TYPE = 'DM' and C.TRANSACTION_TYPE = 'DIST'))
and C.POSTED >= 2 and C.BT_ID=@Id
group by C.BT_ID, C.TRANS_NUMBER, C.OWNER_ORG_CODE, C.PRODUCT_CODE
END
if @IncludeDues=1
BEGIN
insert into #FRDonationTemp(ID,OriginalTransNumber,TransactionNumber,InvoiceRefNum,SourceSystem,
TransactionDate,Amount,AdjustmentAmount,PaymentAmount,Product,Appeal,Campaign,Fund,GiftType,
MatchOrSoftCredit,PaymentType,AdjustmentFlag,ProductMajor,MemTribId,MemTribNameText,ListAs,
SolicitorId,FiscalPeriod,Include)
select max(T.BT_ID) ID,
'' as OriginalTransNumber,
max(Invoice.ORIGINATING_TRANS_NUM) TransactionNumber,
max(T.INVOICE_REFERENCE_NUM) as InvoiceRefNum,
'Dues' as SourceSystem,
max(T.TRANSACTION_DATE) as TransactionDate,
(sum(T.AMOUNT) * -1) as Amount,
(sum(T.AMOUNT) * -1) as AdjustmentAmount,
0 as PaymentAmount,
max(T.PRODUCT_CODE) Product,
'' as Appeal,
'' as Campaign,
max(T.OWNER_ORG_CODE) Fund,
'Pledge' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'Yes' as AdjustmentFlag,
'' as ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
0 as FiscalPeriod,
0 as Include
from Trans T
inner join Invoice on Invoice.REFERENCE_NUM = T.INVOICE_REFERENCE_NUM
where Invoice.SOURCE_SYSTEM='DUES'
and ((T.JOURNAL_TYPE = 'CM' and T.TRANSACTION_TYPE = 'DIST')
or (T.JOURNAL_TYPE = 'DM' and T.TRANSACTION_TYPE = 'DIST'))
and T.IS_FR_ITEM=1 and T.POSTED >= 2 and T.BT_ID=@Id and T.BT_ID=T.ST_ID
group by T.BT_ID, T.TRANS_NUMBER, T.OWNER_ORG_CODE, T.PRODUCT_CODE
UNION ALL
select max(T.ST_ID) ID,
'' as OriginalTransNumber,
max(Invoice.ORIGINATING_TRANS_NUM) TransactionNumber,
max(T.INVOICE_REFERENCE_NUM) as InvoiceRefNum,
'Dues' as SourceSystem,
'' as TransactionDate,
(sum(T.AMOUNT) * -1) as Amount,
(sum(T.AMOUNT) * -1) as AdjustmentAmount,
0 as PaymentAmount,
max(T.PRODUCT_CODE) Product,
'' as Appeal,
'' as Campaign,
max(T.OWNER_ORG_CODE) Fund,
'Pledge' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'Yes' as AdjustmentFlag,
'' as ProductMajor,
'' as MemTribId,
'' as MemTribNameText,
'' as ListAs,
'' as SolicitorId,
0 as FiscalPeriod,
0 as Include
from Trans T
inner join Invoice on Invoice.REFERENCE_NUM = T.INVOICE_REFERENCE_NUM
where Invoice.SOURCE_SYSTEM='DUES'
and ((T.JOURNAL_TYPE = 'CM' and T.TRANSACTION_TYPE = 'DIST')
or (T.JOURNAL_TYPE = 'DM' and T.TRANSACTION_TYPE = 'DIST'))
and T.IS_FR_ITEM=1 and T.POSTED >= 2 and T.ST_ID=@Id and T.BT_ID<>T.ST_ID
group by T.BT_ID, T.TRANS_NUMBER, T.OWNER_ORG_CODE, T.PRODUCT_CODE
END
if @SelectType in ('Donor History','SummaryData','Summary2','GiftPortfolio')
BEGIN
insert into #FRPayment(ID,OriginalTransNumber,TransactionNumber,DateReceived,InvoiceRefNum,PaymentAmount,GiftType,FiscalPeriod)
select C.ST_ID ID,
max(P.TRANS_NUMBER) OriginalTransNumber,
max(C.TRANS_NUMBER) TransactionNumber,
max(P.EFFECTIVE_DATE) DateReceived,
max(C.INVOICE_REFERENCE_NUM) InvoiceRefNum,
sum(C.AMOUNT) PaymentAmount,
(case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then 'Gift' else 'Pledge Payment' end) as GiftType,
max(C.FISCAL_PERIOD) FiscalPeriod
from Trans C
inner join Invoice on Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
inner join Trans P on P.TRANS_NUMBER = Invoice.ORIGINATING_TRANS_NUM
inner join Activity on Activity.SEQN = P.ACTIVITY_SEQN
where P.LINE_NUMBER = 1 and P.SUB_LINE_NUMBER = 1 and P.JOURNAL_TYPE = 'IN' and
P.TRANSACTION_TYPE = 'DIST' and Invoice.SOURCE_SYSTEM='FR' and C.JOURNAL_TYPE = 'IN' and
C.TRANSACTION_TYPE = 'PAY' and C.ST_ID=@Id
group by C.ST_ID, C.TRANS_NUMBER, C.INVOICE_REFERENCE_NUM
UNION ALL
select C.ST_ID ID,
max(P.TRANS_NUMBER)OriginalTransNumber,
max(P.TRANS_NUMBER)TransactionNumber,
max(C.TRANSACTION_DATE) DateReceived,
max(C.INVOICE_REFERENCE_NUM) InvoiceRefNum,
(sum(C.AMOUNT) * -1) PaymentAmount,
(case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then 'Gift' else 'Pledge Payment' end) as GiftType,
max(C.FISCAL_PERIOD) FiscalPeriod
from Trans C
inner join Invoice on Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
inner join Trans P on P.TRANS_NUMBER = Invoice.ORIGINATING_TRANS_NUM
inner join Activity on Activity.SEQN = P.ACTIVITY_SEQN
where P.LINE_NUMBER = 1 and P.SUB_LINE_NUMBER = 1 and P.JOURNAL_TYPE = 'IN'
and P.TRANSACTION_TYPE = 'DIST' and Invoice.SOURCE_SYSTEM='FR'
and C.JOURNAL_TYPE = 'PAY' and C.TRANSACTION_TYPE = 'AR' and C.ST_ID=@Id
group by C.ST_ID,P.TRANS_NUMBER
UNION ALL
select P.ST_ID ID,
max(P.TRANS_NUMBER) OriginalTransNumber,
max(C.TRANS_NUMBER) TransactionNumber,
max(P.EFFECTIVE_DATE) DateReceived,
max(P.INVOICE_REFERENCE_NUM) InvoiceRefNum,
sum(C.INVOICE_CREDITS) PaymentAmount,
(case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then 'Gift' else 'Pledge Payment' end) as GiftType,
max(C.FISCAL_PERIOD) FiscalPeriod
from Trans C
inner join Invoice on Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
inner join Trans P on P.TRANS_NUMBER = Invoice.ORIGINATING_TRANS_NUM
inner join Activity on Activity.SEQN = P.ACTIVITY_SEQN
where P.LINE_NUMBER = 1 and P.SUB_LINE_NUMBER = 1 and P.JOURNAL_TYPE = 'IN' and
P.TRANSACTION_TYPE = 'DIST' and Invoice.SOURCE_SYSTEM='FR'
and ((C.JOURNAL_TYPE = 'PAY' and C.TRANSACTION_TYPE = 'PAY') or (C.JOURNAL_TYPE = 'IN' and C.TRANSACTION_TYPE = 'TR'))
and P.ST_ID=@Id
group by P.ST_ID, P.TRANS_NUMBER, P.INVOICE_REFERENCE_NUM
if @IncludeDues=1
BEGIN
insert into #FRPayment(ID,OriginalTransNumber,TransactionNumber,DateReceived,InvoiceRefNum,PaymentAmount,GiftType,FiscalPeriod)
select C.BT_ID ID,
max(P.TRANS_NUMBER) OriginalTransNumber,
max(C.TRANS_NUMBER) TransactionNumber,
max(C.TRANSACTION_DATE) DateReceived,
max(C.INVOICE_REFERENCE_NUM) InvoiceRefNum,
sum(Activity.AMOUNT) PaymentAmount,
'Gift' as GiftType,
max(C.FISCAL_PERIOD) FiscalPeriod
from Trans C
inner join Trans P on P.TRANS_NUMBER = C.TRANS_NUMBER
inner join Activity on Activity.ORIGINATING_TRANS_NUM = P.TRANS_NUMBER
where Activity.ACTIVITY_TYPE='GIFT' and P.LINE_NUMBER = 1 and
P.SUB_LINE_NUMBER = 1 and P.JOURNAL_TYPE = 'PAY' and
P.TRANSACTION_TYPE = 'DIST' and P.IS_FR_ITEM=1 and C.JOURNAL_TYPE = 'PAY' and
C.TRANSACTION_TYPE = 'PAY' and C.BT_ID=@Id
group by C.BT_ID, C.TRANS_NUMBER, C.INVOICE_REFERENCE_NUM
UNION ALL
select T.ST_ID ID,
max(I.ORIGINATING_TRANS_NUM) OriginalTransNumber,
max(T.TRANS_NUMBER) TransactionNumber,
max(T.TRANSACTION_DATE) DateReceived,
max(T.INVOICE_REFERENCE_NUM) InvoiceRefNum,
sum(T.AMOUNT)*-1 PaymentAmount,
'Pledge Payment' as GiftType,
max(T.FISCAL_PERIOD) FiscalPeriod
from Trans T
inner join Invoice I on I.REFERENCE_NUM = T.INVOICE_REFERENCE_NUM
inner join Invoice_Lines IL on IL.REFERENCE_NUM = T.INVOICE_REFERENCE_NUM
where T.IS_FR_ITEM=1 and ((T.JOURNAL_TYPE = 'IN' and T.TRANSACTION_TYPE='PAY')
or (T.JOURNAL_TYPE = 'PAY' and T.TRANSACTION_TYPE='AR'))
and T.ST_ID=@Id and I.BT_ID=IL.ST_ID
and T.INVOICE_LINE_NUM=IL.LINE_NUM
and I.SOURCE_SYSTEM = 'DUES'
group by T.ST_ID,I.ORIGINATING_TRANS_NUM
UNION ALL
select T.ST_ID ID,
max(I.ORIGINATING_TRANS_NUM) OriginalTransNumber,
max(T.TRANS_NUMBER) TransactionNumber,
max(T.TRANSACTION_DATE) DateReceived,
max(T.INVOICE_REFERENCE_NUM) InvoiceRefNum,
sum(T.AMOUNT)*-1 PaymentAmount,
'Pledge Payment' as GiftType,
max(T.FISCAL_PERIOD) FiscalPeriod
from Trans T
inner join Invoice I on I.REFERENCE_NUM = T.INVOICE_REFERENCE_NUM
inner join Invoice_Lines IL on IL.REFERENCE_NUM = T.INVOICE_REFERENCE_NUM
where T.IS_FR_ITEM=1 and ((T.JOURNAL_TYPE = 'IN' and T.TRANSACTION_TYPE='PAY')
or (T.JOURNAL_TYPE = 'PAY' and T.TRANSACTION_TYPE='AR'))
and IL.ST_ID=@Id
and T.INVOICE_LINE_NUM=IL.LINE_NUM
and I.SOURCE_SYSTEM = 'DUES'
group by T.ST_ID,I.ORIGINATING_TRANS_NUM
UNION
select A.ID,
A.ORIGINATING_TRANS_NUM OriginalTransNumber,
max(A.ORIGINATING_TRANS_NUM) TransactionNumber,
max(A.TRANSACTION_DATE) DateReceived,
max(T.INVOICE_REFERENCE_NUM) as InvoiceRefNum,
sum(A.AMOUNT) PaymentAmount,
'Gift' as GiftType,
max(T.FISCAL_PERIOD) FiscalPeriod
from Activity A
inner join Trans T on T.ACTIVITY_SEQN=A.SEQN
where A.ACTIVITY_TYPE in ('GIFT','DUES') and T.IS_FR_ITEM=1 and T.JOURNAL_TYPE = 'PAY' and
T.TRANSACTION_TYPE = 'DIST' and A.ID=@Id and T.BT_ID = @Id
and (A.SOURCE_SYSTEM = 'DUES' or A.SOURCE_SYSTEM = 'SC')
group by A.ID,A.ORIGINATING_TRANS_NUM
UNION
select A.ID,
A.ORIGINATING_TRANS_NUM OriginalTransNumber,
max(A.ORIGINATING_TRANS_NUM) TransactionNumber,
max(A.TRANSACTION_DATE) DateReceived,
max(T.INVOICE_REFERENCE_NUM) as InvoiceRefNum,
sum(A.AMOUNT) PaymentAmount,
'Gift' as GiftType,
max(T.FISCAL_PERIOD) FiscalPeriod
from Activity A
inner join Trans T on T.ACTIVITY_SEQN=A.SEQN
where A.ACTIVITY_TYPE in ('GIFT','DUES') and T.IS_FR_ITEM=1 and T.JOURNAL_TYPE = 'PAY' and
T.TRANSACTION_TYPE = 'DIST' and A.ID=@Id and T.ST_ID = @Id and T.ST_ID <> T.BT_ID
and (A.SOURCE_SYSTEM = 'DUES' or A.SOURCE_SYSTEM = 'SC')
group by A.ID,A.ORIGINATING_TRANS_NUM
END
END
insert into #FRDonation(ID,OriginalTransNumber,TransactionNumber,InvoiceRefNum,SourceSystem,
TransactionDate,Amount,AdjustmentAmount,PaymentAmount,Product,Appeal,Campaign,Fund,GiftType,
MatchOrSoftCredit,PaymentType,AdjustmentFlag,ProductMajor,MemTribId,MemTribNameText,ListAs,
SolicitorId,FiscalPeriod,Include)
select max(ID) ID,
max(OriginalTransNumber) OriginalTransNumber,
max(TransactionNumber) TransactionNumber,
max(InvoiceRefNum) as InvoiceRefNum,
max(SourceSystem) as SourceSystem,
max(TransactionDate) TransactionDate,
sum(Amount) Amount,
sum(AdjustmentAmount) AdjustmentAmount,
sum(PaymentAmount) PaymentAmount,
max(Product) Product,
max(Appeal) Appeal,
max(Campaign) Campaign,
max(Fund) Fund,
max(GiftType) GiftType,
max(MatchOrSoftCredit) MatchOrSoftCredit,
max(PaymentType) as PaymentType,
max(AdjustmentFlag) AdjustmentFlag,
max(ProductMajor) ProductMajor,
max(MemTribId) MemTribId,
max(MemTribNameText) MemTribNameText,
max(ListAs) ListAs,
max(SolicitorId) SolicitorId,
max(FiscalPeriod) FiscalPeriod,
max(Include) Include
from #FRDonationTemp
group by ID, TransactionNumber, Fund, Product
if @IncludeInKind=0
BEGIN
delete #FRDonation where PaymentType='In Kind'
END
if @IncludeMatching=0
BEGIN
delete #FRDonation where MatchOrSoftCredit='Matching'
END
if @SelectType='SummaryData'
BEGIN
select @CurrentYear=convert(char(4),datepart(year,getdate()))+'0101'
select sum(Amount) Amount,max(TransactionDate) TransactionDate into #MyTemp from #FRDonation
group by TransactionNumber,InvoiceRefNum
select @Largest=max(Amount),@DateLargest=max(TransactionDate) from #MyTemp group by Amount
if @OnlyPaidPortion=0
begin
select @Lifetime=sum(Amount) from #FRDonation
end
else
begin
select @Lifetime=isnull(sum(Amount),0) from #FRDonation where GiftType='Gift'
and MatchOrSoftCredit not in ('Matching','Soft Credit')
select @Lifetime=@Lifetime+isnull(sum(PaymentAmount),0) from #FRPayment
where GiftType='Pledge Payment'
end
select @DonorSince=min(TransactionDate) from #FRDonation
if @OnlyPaidPortion=0
begin
select @YTD=sum(Amount) from #FRDonation where TransactionDate>=@CurrentYear
end
else
begin
select @YTD=isnull(sum(Amount),0) from #FRDonation where TransactionDate>=@CurrentYear and GiftType='Gift'
and MatchOrSoftCredit not in ('Matching','Soft Credit')
select @YTD=@YTD+isnull(sum(PaymentAmount),0) from #FRPayment
where GiftType='Pledge Payment' and DateReceived>=@CurrentYear
end
select isnull(@Lifetime,0),isnull(@DonorSince,''),isnull(@Largest,0),isnull(@DateLargest,''),isnull(@YTD,0)
END
if @SelectType='GiftPortfolio'
BEGIN
select sum(Amount) Amount,max(TransactionDate) TransactionDate,max(GiftType) GiftType into #MyTemp2 from #FRDonation
group by TransactionNumber,InvoiceRefNum
select @Largest=max(Amount) from #MyTemp2 where GiftType='Gift' group by Amount
select @PledgeLargest=max(Amount) from #MyTemp2 where GiftType='Pledge' group by Amount
select @Lifetime=sum(Amount) from #FRDonation where GiftType='Gift'
if @OnlyPaidPortion=0
begin
select @PledgeLifetime=isnull(sum(Amount),0) from #FRDonation where GiftType='Pledge'
end
else
begin
select @PledgeLifetime=isnull(sum(PaymentAmount),0) from #FRPayment where GiftType='Pledge Payment'
end
select @DatePledgeLast=max(TransactionDate) from #FRDonation where GiftType='Pledge'
select @DateGiftLast=max(TransactionDate) from #FRDonation where GiftType='Gift'
select @PledgeBalance=isnull(sum(i.BALANCE),0) from Invoice i
where i.BT_ID=@Id and i.SOURCE_SYSTEM = 'FR' and i.ORIGINATING_TRANS_NUM>0 and i.INSTALL_BILL_DATE is not null
select @PledgeBalance=@PledgeBalance+isnull(sum(BALANCE),0) from Invoice_Lines where ST_ID=@Id and IS_FR_ITEM=1
select isnull(@Lifetime,0),isnull(@PledgeLifetime,0),isnull(@Largest,0),isnull(@PledgeLargest,0),
isnull(@DatePledgeLast,''),isnull(@DateGiftLast,''),isnull(@PledgeBalance,0)
END
if @IncludeOutrightGifts=0
BEGIN
delete #FRDonation where GiftType='Gift' and MatchOrSoftCredit not in ('Matching','Soft Credit')
END
if @IncludePledges=0
BEGIN
delete #FRDonation where GiftType in ('Pledge','Pledge Payment') and MatchOrSoftCredit not in ('Matching','Soft Credit')
END
update #FRDonation set GiftType=GiftType+'*' where MatchOrSoftCredit<>''
if @IncludeInKind=1
BEGIN
update #FRDonation set GiftType=GiftType+char(134) where PaymentType='In Kind'
END
select @Include=0
if @FundFilter<>''
begin
update #FRDonation set Include=1 where charindex(Fund,@FundFilter)>0
select @Include=1
end
if @CampaignFilter<>''
BEGIN
if @FundFilter<>''
begin
update #FRDonation set Include=2 where charindex(Campaign,@CampaignFilter)>0 and Include=1
select @Include=2
end
else
begin
update #FRDonation set Include=1 where charindex(Campaign,@CampaignFilter)>0
select @Include=1
end
END
if @DistributionFilter<>''
BEGIN
if @FundFilter<>'' and @CampaignFilter<>''
begin
update #FRDonation set Include=3 where charindex(';'+Product+';',@DistributionFilter)>0 and Include=2
select @Include=3
end
if @FundFilter<>'' and @CampaignFilter=''
begin
update #FRDonation set Include=2 where charindex(';'+Product+';',@DistributionFilter)>0 and Include=1
select @Include=2
end
if @FundFilter='' and @CampaignFilter<>''
begin
update #FRDonation set Include=2 where charindex(';'+Product+';',@DistributionFilter)>0 and Include=1
select @Include=2
end
if @FundFilter='' and @CampaignFilter=''
begin
update #FRDonation set Include=1 where charindex(';'+Product+';',@DistributionFilter)>0 and Include=0
select @Include=1
end
END
if @DoSplits=1
BEGIN
declare TheCursor cursor for
select TransactionNumber,Fund,SourceSystem,Product,Campaign
from #FRDonation where SourceSystem<>'Event' and GiftType<>'' order by TransactionNumber
open TheCursor
fetch next from TheCursor into @TransNum,@Fund,@Source,@Product,@Campaign
WHILE @@FETCH_STATUS = 0
BEGIN
if @TransNum=@PrevTransNum and @Fund=@PrevFund and @Product<>@PrevProduct
begin
update #FRDonation set Product='-Split-' where TransactionNumber=@TransNum
end
if @PrevFund<>''
begin
if @TransNum=@PrevTransNum and @Fund<>@PrevFund
begin
update #FRDonation set Fund='-Split-',Product='-Split-' where TransactionNumber=@TransNum
end
end
if @PrevCampaign<>''
begin
if @TransNum=@PrevTransNum and @Campaign<>@PrevCampaign and @Campaign<>''
begin
update #FRDonation set Campaign='-Split-' where TransactionNumber=@TransNum
end
end
begin
select @PrevTransNum=@TransNum
select @PrevFund=@Fund
select @PrevCampaign=@Campaign
select @PrevProduct=@Product
end
fetch next from TheCursor into @TransNum,@Fund,@Source,@Product,@Campaign
END
close TheCursor
deallocate TheCursor
END
select @PrevFund=''
if charindex(',',@FundFilter)=0
BEGIN
declare TheCursor cursor for
select InvoiceRefNum,Fund,ProductMajor,Product,Campaign
from #FRDonation where SourceSystem='Event' order by InvoiceRefNum
open TheCursor
fetch next from TheCursor into @InvoiceRefNum,@Fund,@ProductMajor,@Product,@Campaign
WHILE @@FETCH_STATUS = 0
BEGIN
if @ProductMajor=@EventCode and @Fund=@PrevFund
begin
update #FRDonation set Product='-Split-', TransactionNumber=InvoiceRefNum where InvoiceRefNum=@InvoiceRefNum
end
if @PrevFund<>''
begin
if @ProductMajor=@EventCode and @Fund<>@PrevFund and @Fund<>''
begin
update #FRDonation set Fund='-Split-',Product='-Split-', TransactionNumber=InvoiceRefNum where InvoiceRefNum=@InvoiceRefNum
end
end
if @PrevCampaign<>''
begin
if @ProductMajor=@EventCode and @Campaign<>@PrevCampaign and @Campaign<>''
begin
update #FRDonation set Campaign='-Split-', TransactionNumber=InvoiceRefNum where InvoiceRefNum=@InvoiceRefNum
end
end
begin
select @EventCode=@ProductMajor
select @PrevFund=@Fund
select @PrevCampaign=@Campaign
end
fetch next from TheCursor into @InvoiceRefNum,@Fund,@ProductMajor,@Product,@Campaign
END
close TheCursor
deallocate TheCursor
END
delete #FRDonation from #FRDonation f where f.OriginalTransNumber = 0 and f.AdjustmentFlag = 'Yes'
and NOT EXISTS(select 1 from Trans t where t.JOURNAL_TYPE = 'IN' and t.TRANSACTION_TYPE = 'DIST'
and (t.ACTIVITY_TYPE = 'PLEDGE' or t.ACTIVITY_TYPE = 'GIFT') and t.INVOICE_REFERENCE_NUM = f.InvoiceRefNum)
if @SelectType='Summary1'
BEGIN
select @Sql='select TOP '+convert(varchar(3),@TopLast)+' max(TransactionDate) Date,
sum(Amount) Amount,max(GiftType) Type,max(SourceSystem) Source
from #FRDonation group by TransactionNumber,InvoiceRefNum'
if @TopWhat<>'Donations'
begin
select @Sql=@Sql+' having max(GiftType)='+''''+convert(varchar(10),@TopWhat)+''''
end
select @Sql=@Sql+' order by max(TransactionDate) Desc,TransactionNumber'
exec (@Sql)
END
if @SelectType='Summary2'
BEGIN
if @OnlyPaidPortion=0
begin
insert into #Trends select TransactionNumber,max(substring(convert(char(6),FiscalPeriod),1,4)),
sum(Amount),0 from #FRDonation group by TransactionNumber,InvoiceRefNum
end
else
begin
insert into #Trends select TransactionNumber,max(substring(convert(char(6),FiscalPeriod),1,4)),
sum(Amount),0 from #FRDonation where GiftType in ('Gift','Gift*','Pledge*') group by TransactionNumber,InvoiceRefNum
insert into #Trends select TransactionNumber,max(substring(convert(char(6),FiscalPeriod),1,4)),
sum(PaymentAmount),0 from #FRPayment where GiftType='Pledge Payment' group by TransactionNumber
end
declare TheCursor cursor for
select max(TheYear),sum(Amount) from #Trends
group by TheYear order by max(TheYear) Asc
open TheCursor
fetch next from TheCursor into @Year,@Amount
WHILE @@FETCH_STATUS = 0
BEGIN
if @PrevYear<>''
begin
select @RoundAmount=((@Amount-@PrevAmount)/@PrevAmount)*100
update #Trends set Calc=ROUND(@RoundAmount,0) where TheYear=@Year
end
select @PrevYear=@Year
select @PrevAmount=@Amount
fetch next from TheCursor into @Year,@Amount
END
close TheCursor
deallocate TheCursor
select @Sql='select TOP '+convert(varchar(2),@TopYears)+' max(TheYear) Year,
sum(Amount) Amount,sum(Amount)/count(TransactionNumber) as Average,convert(varchar(25),max(Calc))+''%'' Calc from #Trends
group by TheYear order by max(TheYear) Desc'
exec (@Sql)
END
if @SelectType='Donor History'
BEGIN
select @Sql='insert into #FRDonationHistory (TransactionNumber,InvoiceRefNum,TransactionDate,GiftType,MatchOrSoftCredit,
SourceSystem,Campaign,Fund,Product,Amount,PaymentAmount,AdjustmentFlag,Include)
select TransactionNumber,InvoiceRefNum,max(TransactionDate) TransactionDate,max(GiftType) GiftType,
max(MatchOrSoftCredit) MatchOrSoftCredit,max(SourceSystem) SourceSystem,max(Campaign) Campaign,max(Fund) Fund,max(Product) Product,
sum(Amount) Amount,sum(PaymentAmount) PaymentAmount,max(AdjustmentFlag) AdjustmentFlag,max(Include) Include
from #FRDonation group by TransactionNumber,InvoiceRefNum,Product'
exec (@Sql)
select max(ID) ID,OriginalTransNumber,max(TransactionNumber) TransactionNumber,
max(DateReceived) DateReceived,max(InvoiceRefNum) InvoiceRefNum,sum(PaymentAmount) PaymentAmount,
max(GiftType) GiftType,max(FiscalPeriod) FiscalPeriod into #TempPayments from #FRPayment group by OriginalTransNumber
delete #FRPayment
insert #FRPayment select * from #TempPayments
update #FRDonationHistory set #FRDonationHistory.PaymentAmount=#FRPayment.PaymentAmount from #FRDonationHistory
join #FRPayment on #FRPayment.OriginalTransNumber=#FRDonationHistory.TransactionNumber
where #FRPayment.OriginalTransNumber=#FRDonationHistory.TransactionNumber and #FRDonationHistory.GiftType<>''
update #FRDonationHistory set Product='' where GiftType=''
if @FromDate<>''
BEGIN
delete #FRDonationHistory where TransactionDate<@FromDate
END
if @ThroughDate<>''
BEGIN
delete #FRDonationHistory where TransactionDate>@ThroughDate
END
select @MidTermAmount=0.00
if @IncludeMidTerm=1
begin
select @MidTermAmount=isnull(sum(PaymentAmount),0) from #FRPayment where GiftType='Pledge Payment'
and TransactionNumber not in (select TransactionNumber from #FRDonationHistory)
end
select @HistoryRowCount=0
select @HistoryRowCount=count(*) from #FRDonationHistory
if @HistoryRowCount>0
BEGIN
select @Sql='select TransactionNumber,max(InvoiceRefNum),max(TransactionDate) Date,max(GiftType) Type,SourceSystem Source,
max(Campaign) Campaign,max(Fund) Fund,max(Product) Distribution_Product_Function,
sum(Amount) Total_Donation,sum(PaymentAmount) Amount_Paid,max(AdjustmentFlag) Adj,
case
when max(SourceSystem)=''Event'' then 0
when max(MatchOrSoftCredit)=''Soft Credit'' then 0
else sum(Amount)-sum(PaymentAmount) end as Balance,'
+convert(varchar(10),@MidTermAmount)+' as MidTermAmount
from #FRDonationHistory'
if @Include>0
begin
select @Sql=@Sql+' where Include='+convert(varchar(1),@Include)+' group by TransactionNumber,SourceSystem'
end
else
begin
select @Sql=@Sql+' group by TransactionNumber,SourceSystem'
end
if @FromAmount>0 and @ThroughAmount>0
BEGIN
select @Sql=@Sql+' having sum(Amount)>='+convert(varchar(10),@FromAmount)
+' and sum(Amount)<='+convert(varchar(10),@ThroughAmount)
END
if @FromAmount>0 and @ThroughAmount=0
BEGIN
select @Sql=@Sql+' having sum(Amount)>='+convert(varchar(10),@FromAmount)
END
if @FromAmount=0 and @ThroughAmount>0
BEGIN
select @Sql=@Sql+' having isnull(sum(Amount),0)<='+convert(varchar(10),@ThroughAmount)
END
if @IncludeWrittenOff=0
BEGIN
select @Sql=@Sql+
case
when @FromAmount>0 or @ThroughAmount>0
then ' and sum(Amount)>0.00'
else ' having sum(Amount)>0.00'
end
END
END
ELSE
BEGIN
select @Sql='select 0 TransactionNumber,0 InvoiceRefNum,'''' Date,'''' Type,'''' Source,
'''' Campaign,'''' Fund,'''' Distribution_Product_Function,0.00 Total_Donation,0.00 Amount_Paid,0 Adj,0 Balance,
' +convert(varchar(10),@MidTermAmount)+' MidTermAmount'
END
exec (@Sql)
END
GO
GRANT EXECUTE ON [dbo].[sp_asi_FRDonations] TO [IMIS]
GO
[dbo].[sp_asi_FRDonations]